/**
* file name  : it/ciano/jenoware/PgSQL.java
* authors    : Luciano Xumerle
* created    : ven 24 set 2004 17:24:51 CEST
*
* Copyright (c) 2004-2005 Luciano Xumerle. All rights reserved.
*
* This file is part of gephinfo.
*
* gephinfo is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program.  If not, see <http://www.gnu.org/licenses/>.
*
*/

package it.ciano.jenoware;

import java.io.UnsupportedEncodingException;
import java.lang.NullPointerException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Class to manages the PostgreSQL JDBC driver.
 * <br>
 * The constructor stores the values of the hostname and the database name.
 * <br>
 * The method connect[databasename] (i.e. <i>connectPostgreSQL</i>) initialize the
 * connection to the database.
 * <br>
 * The method <i>closeDB</i> close the connection.
 *
 *
 * @author Luciano Xumerle
 * @version 0.0.3
 */
public class PgSQL {
    private Connection db;
    private Statement st;
    private String _hostname;
    private String _dbname;
    private boolean _isConnect;

    /**
     * Class constructor.
     *
     * @param databaseName database name.
     */
    public PgSQL ( String databaseName ) {
        this ( null, databaseName );
    }

    /**
     * Class constructor.
     *
     * @param hostname host name.
     * @param databaseName database name.
     */
    public PgSQL ( String hostname, String databaseName ) {
        if ( hostname == null )
            _hostname = "localhost";
        else
            _hostname = hostname;
        _dbname = databaseName;
        _isConnect=false;
    }

    /**
     * Connect to a PostgreSQL database.
     *
     * @param loginUser user name.
     * @param loginPasswd password.
     */
    public boolean connectPostgreSQL ( String loginUser, String loginPasswd ) {
        _isConnect=false;
        try {
            Class.forName ( "org.postgresql.Driver" );
            StringBuffer loginUrl = new StringBuffer();
            loginUrl.append ( "jdbc:postgresql://" );
            loginUrl.append ( _hostname );
            loginUrl.append ( "/" );
            loginUrl.append ( _dbname );
            db = DriverManager.getConnection ( loginUrl.toString(), loginUser, loginPasswd );
            st = db.createStatement();
            _isConnect=true;
            return _isConnect;
        } catch ( ClassNotFoundException ex ) {
            System.err.println ( "ClassNotFoundException: " + ex.getMessage() );
        } catch ( SQLException ex ) {
            System.err.println ( "SQLException: " + ex.getMessage() );
        }
        return false;
    }

    /**
     * Close PostgreSQL database connection.
     */
    public void closeDB() {
        try {
            if ( _isConnect ) {
                st.close();
                db.close();
            }
        } catch ( SQLException ex ) {
            System.err.println ( "SQLException: " + ex.getMessage() );
        }
    }

    /**
     * Returns the quoted String or null if the input is an empty String.
     *
     * @param in String to be quoted.
     * @return the quoted String or null if the input is an empty String.
     */
    final static public String getQuote ( String in ) {
        if ( in==null || in.trim().equals ( "" ) )
            return "null";
        char[] ch = in.trim().toCharArray();
        StringBuffer res=new StringBuffer();
        res.append ( "'" );
        int index = 0;
        while ( index < ch.length ) {
            if ( ch[ index ]=='\''  )
                res.append ( "'" );
            res.append ( ch[ index++ ] );
        }
        res.append ( "'" );
        return res.toString();
    }

    /**
     * Executes a SQL query and returns the ResultSet.
     *
     * @param sql the query.
     * @return the ResultSet object. Is null with no valid SQL.
     */
    public ResultSet executeSQL ( String sql ) {
        try {
            return st.executeQuery ( sql );
        } catch ( Exception ex ) {
            System.err.println ( "Error in executeSQL: " + ex.getMessage() );
        }
        return null;
    }

    /**
     * Executes a SQL command.
     *
     * @param sql The sql command.
     */
    public boolean doSQL ( String sql ) {
        try {
            st.executeUpdate ( sql );
        } catch ( Exception ex ) {
            System.err.println ( "SQLException: " + ex.getMessage() );
            return false;
        }
        return true;
    }

    /**
     * Returns the bidimensional table containing the data.
     *
     * @param rs the result of a query.
     * @return the bidimensional table containing the data.
     */
    public String[][] toStringTable ( ResultSet rs ) {
        ArrayList res=new ArrayList();
        try {
            int num=rs.getMetaData().getColumnCount();
            while ( rs != null && rs.next() ) {
                String rr[] = new String[num];
                for ( int i=0; i<num; i++ ) {
                    try {
                        rr[i]=new String ( rs.getBytes ( i+1 ), "ISO-8859-1" );
                    } catch ( java.io.UnsupportedEncodingException e ) {
                        rr[i]=rs.getString ( i+1 );
                    } catch ( java.lang.NullPointerException e ) {
                        rr[i]="";
                    }
                }
                res.add ( rr );
            }
        } catch ( java.sql.SQLException e ) {
            System.err.println ( e.toString() );
            return null;
        }
        if ( res.size() ==0 ) return null;
        String[][] pp=new String[res.size() ][];
        for ( int i=0; i<pp.length; i++ )
            pp[i]= ( String[] ) res.get ( i );
        return pp;
    }

    /**
     * Returns an array with the attributes of the given table.
     *
     * @param table name of the table.
     * @return the array.
     */
    public String[] getAttributeList ( String table ) {
        try {
            return getAttributeList ( st.executeQuery ( sql2check ( table ) ) );
        } catch ( Exception ex ) {
            System.err.println ( "Error in getAttributeList: " + ex.getMessage() );
        }
        return null;
    }

    /**
     * Returns an array with the attributes of the given table.
     *
     * @param rs the ResultSet object.
     * @return the array.
     */
    public String[] getAttributeList ( ResultSet rs ) {
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            String res[] = new String[ cols ];
            for ( int i = 1; i <= cols; i++ )
                res[ i - 1 ] = rsmd.getColumnLabel ( i );
            return res;
        } catch ( Exception ex ) {
            System.err.println ( "Error in getAttributeList: " + ex.getMessage() );
        }
        return null;
    }

    /**
     * Returns the number of colums for the given ResultSet.
     *
     * @param rs the ResultSet object.
     * @return the int value.
     */
    public int getNumberOfColums ( ResultSet rs ) {
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            return rsmd.getColumnCount();
        } catch ( Exception ex ) {
            System.err.println ( "Error in getNumberOfColums: " + ex.getMessage() );
        }
        return 0;
    }

    /**
     * Returns true if the SQL statement has more than zero rows as result.
     *
     * @param sql an SQL statement.
     * @return true or false.
     */
    public boolean hasResultsSet ( String sql ) {
        try {
            ResultSet rs = st.executeQuery ( sql );
            return rs.next();
        } catch ( Exception ex ) {
            return false;
        }
    }

    /**
     * Returns true if the table exists.
     *
     * @param table a table name.
     * @return true or false.
     */
    public boolean isPresent ( String table ) {
        try {
            st.executeQuery ( sql2check ( table ) );
        } catch ( Exception ex ) {
            return false;
        }
        return true;
    }


    /**
     * Returns true if the attribute into the table exists.
     *
     * @param table a table name.
     * @param attribute  an attribute name.
     * @return true or false.
     */
    public boolean isAttributePresent ( String table, String attribute ) {
        try {
            st.executeQuery ( sql2check ( table, attribute ) );
        } catch ( Exception ex ) {
            return false;
        }
        return true;
    }

    /**
     * Returns the attribute SQL type for the given attribute of a table.
     *
     * @param table the table.
     * @param attribute the attribute.
     * @return the SQL type of attribute.
     */
    public String getAttributeType ( String table, String attribute ) {
        String sql = "SELECT CASE "
                + "WHEN attlen = -1 THEN "
                + "CASE "
                + "WHEN typname = 'varchar' "
                + "THEN typname || '(' || atttypmod - 4 || ')' "
                + "WHEN typname = 'bpchar' "
                + "THEN 'char' || '(' || atttypmod - 4 || ')' "
                + "WHEN typname = 'numeric' "
                + "THEN format_type(atttypid, atttypmod) "
                + "ELSE typname END "
                + "ELSE typname END AS tipo "
                + "FROM pg_attribute, pg_type, pg_class "
                + "WHERE attrelid=relfilenode AND pg_type.oid=pg_attribute.atttypid "
                + "AND  relname='" + table + "' and attname='" + attribute + "'";
        try {
            ResultSet rs = st.executeQuery ( sql );
            if ( rs.next() ) {
                return rs.getString ( 1 );
            }
        } catch ( Exception ex ) {
            return "";
        }

        return "";
    }


    /**
     * Returns the integer to be used as id for the next record.
     * The method is useful to replace deleted ids.
     *
     * @param table Table name.
     * @param attribute the attibute used as id.
     * @return the integer.
     */
    public int getNewID ( String table, String attribute ) {
        int oldid=0;
        int current=1;
        String sql="SELECT " + attribute + " FROM " + table + " ORDER BY " + attribute;
        try {
            ResultSet rs = st.executeQuery ( sql );
            while ( rs != null && rs.next() ) {
                current=rs.getInt ( 1 );
                if ( oldid==0 ) {
                    oldid=current;
                    continue;
                }
                if ( current-oldid>1 ) return current-1;
                oldid=current;
            }
        } catch ( Exception ex ) {
            return current+1;
        }
        return oldid+1;
    }


    /**
     * Adds to the Pedigree the values contained into a given database table.
     *
     * @param ped a Pedigree object.
     * @param querySQL an SQL query.
     * @param userFixedFields user fixed fields
     * (eg. "fml", "sbj", "fth", "mth", "sex", "prob", "id_ext").
     * @return true or false.
     */
    public boolean updatePedigree ( Pedigree ped, String querySQL,  String[] userFixedFields ) {
        try {
            String[] field=userFixedFields;
            ResultSet rs = executeSQL ( querySQL );

            if ( field==null )
                field=CsvHead.fixedFields;
            CsvHead   head = new CsvHead ( getAttributeList ( rs ), field );

            int cols = getNumberOfColums ( rs );
            while ( rs != null && rs.next() ) {
                String res[] = new String[ cols ];
                for ( int i = 1; i <= cols; i++ )
                    res[ i - 1 ] = rs.getString ( i );
                head.addRow ( ped, res );
            }
            rs.close();
            return true;
        } catch ( Exception ex ) {
            System.err.println ( "SQL Error: " + querySQL );
        }
        return false;
    }

    /**
     * Adds to the Pedigree the values contained into a given database table.
     * The method uses the CsvHead.fixedFields list.
     *
     * @param ped a Pedigree object.
     * @param querySQL an SQL query.
     * @return true or false.
     */
    public boolean updatePedigree ( Pedigree ped, String querySQL ) {
        return updatePedigree ( ped, querySQL, null );
    }

    /**
     * Returns the SQL query used to do some checks.
     *
     * @param table database table.
     * @param attribute attribute of the table.
     * @return the sql query.
     */
    private final static String sql2check ( String table, String attribute ) {
        StringBuffer sql = new StringBuffer();
        sql.append ( "SELECT " );
        if ( attribute == null )
            sql.append ( "*" );
        else
            sql.append ( attribute );
        sql.append ( " FROM " );
        sql.append ( table );
        sql.append ( " WHERE 1=2" );
        return sql.toString();
    }


    /**
     * Add to a SQL query the code needed to use the OFFSET and LIMIT instructions.
     * Each page is composed by X lines.
     *
     * @param selectSQL The query SQL code.
     * @param pageNumber The page to be selected.
     * @param pageLines The dimension X of each page.
     * @return The new SQL command.
     */
    final public static String sqlPageDim ( String selectSQL, int pageNumber, int pageLines ) {
        StringBuffer r = new StringBuffer();
        r.append ( selectSQL );
        r.append ( " OFFSET " );
        r.append ( pageNumber * pageLines );
        r.append ( " LIMIT " );
        r.append ( pageLines );
        return r.toString();
    }


    /**
     * Returns the SQL query used to do some checks.
     *
     * @param table database table.
     * @return the sql query.
     */
    private final static String sql2check ( String table ) {
        return sql2check ( table, null );
    }

} // end class

